There are many multiple-user systems that are currently available for storing and retrieving information. Many of these are database systems that permit system users to obtain large amounts of information on an unrestricted basis. For example, such systems could be con-figured as shown generally at 100 in FIG. 1. This prior art system could have a “hub and spoke” configuration that could be appropriately embodied as a local area network (“LAN”), metropolitan area network (“MAN”), or a wide area network (“WAN”).
According to FIG. 1, the system may include 1 to N system users who interface with the central section of the system through personal computers 102. “N” is an integer greater that “1.” The personal computers may be hardwired to the central section or they connect through a wireless connection. Moreover, personal computers 102 may be connected to the remainder of the system via the Internet or as part of an intranet.
A system user's personal computer may or may not contain the necessary application software for system operation. For example, the application software may reside at the personal computers or at system server 108.
The system designer will determine the most efficient and desirable transmission method for the 1 to N system users to communicate with the central section of the system given the circumstances surrounding system use, system user location, and user demographics. Through the selected communication method, each of the system users will be able to communicate with the system databases to input desired data information to, or retrieve desired data information from, the system databases.
Firewall 106 is shown in FIG. 1. Firewall 106 will pass system data information but will filter other information. Firewall 106 operates in a conventional manner.
Firewall 106 connects to system server 108. System server 108 also operates conventionally. System server 108 is object-oriented based and has a client-side and a server-side. The client-side objects are responsible for maintaining the system state and contains substantially all of operation rules for the system. The server-side objects are stateless and interact with the databases through database server 114. The main purpose of the server-side objects is to ensure transaction integrity.
System server 108 connects to database server 114. Database server 114 server may be a structured-query language (“SQL”) server. There may be one or more SQL database servers associated with the system. The database server is used for accessing the various system databases. SQL database server 114 is associated with specific system databases, such as databases 118 and 120. These databases will store specific data information which may be retrieved if the system user submits the proper query. After the databases are created, system users may also add data information to, or delete data information from, these databases.
Databases 118 and 120 receive and store data information from the various system users or from the system administrator. The stored data information will usually be overlaid with a database management system (DBMS) that is resident in database server 114. The DBMS generally will include database applications. Typically, the DBMS is configured using four general types of data-structure classes. These are hierarchic, network, relational, and semantic data-structure classes. In particular, the relational data-structure class, with its association with DBMS, has evolved into what is now referred to as Relational Databases.
Relational databases are now viewed, in many cases, the standard for the design of both large and small databases. The simplest model of a database would consist of a single table which includes a number of fields for each record that is desired to be stored. If a relational database system is not used, it will be necessary to repeat a great deal of information within the database. This makes it very difficult to maintain the data and there may be resulting data errors based on inconsistent records. However, if a relational database system is used, activities in the database will be related through, e.g., a record. The database application software of a relational database system will manage groups of records that are related.
As an example, if there is a catalog-based sales system, information may be stored in three tables. The tables could be titled Name, Order, Items Ordered. Each of these tables, in turn, would have subsections of information. The subsections for the Name table could include the spelling of the name; the street address; city, state, and zip-code; and phone number. The Order table could have the subsections invoice number, date, and catalog number. Finally, the Items Ordered table could have the subsections catalog item number ordered, quantity, and price. If this information is handled by a relational database system, each of the Order table records will include a name identification (NID) field and the Items Ordered table records will include an order number field (ONB) field. These two new fields are foreign keys of the relational database and provide a serial link for the records of the database.
The creation of the database in the method just described alleviates the need to repeat the Name table data for each Order table record because you can rely on the serial linking, through the NID. Accordingly, if there is, for example, a change in the address associated with a particular name in the Name record, this change will be made for every order associated with that Name. This same process is applied to the second link, ONB.
As stated, database server 114 may be a SQL server. As such, the queries that are developed for relational databases 118 and 120 are in the Structured Query Language (“SQL”). Using SQL to develop a query, the query, in an unrestricted manner, could request the display of all of the orders placed by someone with a particular name. The Name and Orders tables are linked by a NID, and once this linking has taken place, the number of orders may be determined for a particular Name and displayed as desired.
A query in SQL, with regard to obtaining information from the three tables defined previously, would consist of a SELECT command follows by a FROM command. The SELECT command or keyword defines the desire to select records from the tables. This could be SELECT name; street address; city, state, and zip-code; order invoice number; and order date. The command or keyword that follows is FROM. The FROM command or keyword indicates from where the data comes. Accordingly, the FROM command or keyword in the previous example would indicate the data was coming from the Name table and the order table. The FROM command will be associated with the appropriate JOIN command which will provide the proper connection of the data from the two tables.
The decision must be made as to how to maintain the integrity of the database files when there are deletions. If cascading is the desired method, if there is a change in the NID in the Name table, then there would be a change in the related records in the Order table. The second choice is that deletions will not be permitted if there are related records. In this case, if there is a deletion of a NID and there are related records in the Order table, the deletion of the NID will not be permitted until there related records are deleted or are no longer related to the NID being deleted.
Tables are to represent things in the real world. In a particular database, the row of a table is to be unique. Each of the columns represent a specific field for each row. This is carried out by each table having a primary key which is usually a column of the table having unique values for the table. Each table will have only one primary key. Together all of the columns of the table with unique values are candidate keys from which the primary key is selected. The other columns are alternate keys. A simple key is made up of one column where a composite key is made up of two or more columns. The easiest way to choose the primary key is to select one column if you can.
The main use of primary keys is for creating relationships between multiple tables in a database. This concept involves the use of foreign keys mentioned briefly above. A foreign key in a table references a primary key in another table. This joining relationship is the basis of relational database theory. For example, the primary key in a first table would be a foreign key in a second table. These table-to-table relationships may continue infinitely if desired. They may be on an one-to-one relationship, an one-to-many relationship, or a many-to-many relationship, which in reality is multiple one-to-many relationships.
The efficiency of the database tables is accomplished by normalization to remove redundancies from the tables. The three general normalization forms are the First, Second, and Third Norm Forms. The First Norm Form is that for every row-by-column position in a particular table, there is only one value. The Second Norm Form is that every non-primary key column is dependent on the primary key. And, the Third Norm Form is that all of the non-primary key columns are independent of one another. There are higher Norm Forms that solve certain specific inadequacies in the first three, but principally normalization is satisfied by satisfying the First, Second, and Third Norm Forms.
The integrity rules are of two types: General and Database-Specific. The General Integrity rules may be either Entity Integrity or Referential Integrity. The Entity Integrity rule is that the primary keys cannot have missing data. The Referential Integrity rule is that a database must not contain any unmatched foreign key values. In following these rules, if a referenced primary key changes or a row is deleted, the system may take one of three options. First and second it may cascade or prevent the change, as described before. Third, for deletions, the system may set all of the foreign keys values associated with the deletion to zero or null.
Database-Specific Integrity rules are ones that are specific to a particular database. These are ones that the database creator develops for enhancing his/her database and especially database management.
SQL was discussed briefly above as a query language for relational databases. It is particularly useful to create and manipulate relational databases. The SQL commands are divided into two groups. These are Data Definition language (“DDL”) and Data Manipulation Language (“DML”). DDL includes the commands that are used to create and delete databases and databases objects. On the other hand, DML is used to insert, retrieve, and modify data once a database is defined with DDL.
The four basic commands of DDL are CREATE, USE, ALTER, and DROP. CREATE is a command used to establish a database in a system. The CREATE command may also be used to create tables in a database. USE is a command that is used to specify the database that is desired to work within the system. ALTER is a command that is used once a table is created to modify the definitions of it. That is, changes to the structure of the table may be made without deleting and recreating it. DROP is a command that is used to remove entire database structures from a database.
DML, as stated, is used to retrieve, insert, and modify database information. The DML commands INSERT, SELECT, UPDATE and DELETE are used during routine operations of the database. INSERT is a command that is used to add records to an existing table. As an example, INSERT could be used to add new rows to the existing table. SELECT is a command that is used to retrieve specific information from an operational database. It may be tailored narrowly or broadly. UPDATE is a command that is used to modify information contained in and existing table. This may be done for an individual cell or for more than one cell of values. DELETE is a command that is used to remove a record from an existing table.
There are times when the data information that is stored in the databases is not intended to be accessible by all system users, which may be for a number of reasons. For example, the data information could be of a sensitive nature or it simply may not pertain to the activities of a particular system user. Whatever the reason, there is a basis for permitting certain system users access to specific information while, at the same time, denying certain other system users access to this same data information.
This problem has been addressed by providing system users access to the restricted data information using external security coding. The best known and most used method of such coding is the use of passwords. The password concept has been expanded in situations relating to the access to very sensitive data information. In these cases, recognition systems have been used the evaluate certain unique physical attributes of the system user seeking access, such as voice, fingerprints, and retina scans, before access is granted. The problem also has been addressed by requiring the system user seeking access to answer a series of questions that allegedly only the system user having the proper authorization should know the answer.
This conventional way to incorporate levels of security into the system such as is shown in FIG. 1, generally at 100, may be cured out by externally layering security coding at system server 108. This security coding will require the system user to present the proper information before he/she will be granted access to the restricted data information. The problem with this type of security is that it always leaves open the possibility that a cleaver computer programmer or “hacker,” given enough time, will be able to find a “back door” to gain unauthorized access to the restricted data information that is stored in the system databases.
There have been attempts to defeat the ability of hackers to breach system security when such a system has a potential “back door.” One method has been to change the access codes on a scheduled basis. This entailed changing the access code after very short periods of time, such as every day, every week, or every 12 hours. This scheme was believed effective because it allegedly would not give the “hacker” with enough time to be able to obtain the correct access code before it is changed to a new access code. However, these systems have been compromised.
Another method has been to use random number of generators that change the access code every minute. In these systems, the system user and the system would have matched random number generator systems that use the same algorithm. Again unless they matched, access would be denied. The problem with these methods is that the “back door” still exists and those who are authorized access to the restricted data information must always coordinate with the system server to ensure he/she is able to present the correct access codes. In a large commercial company, this solution can result in a nightmare for those who are authorized access to restricted data information because they may not always have the appropriate access codes at their fingertips or the “hackers” figure the access code generating algorithm.
The present invention overcomes these problems as will be set forth in the remainder of this specification and claims, and referring to the drawings.